﻿Imports System
Imports System.Data
Imports System.Data.OracleClient

Public Class ProcedureBackEnd
	Public Sub New()

	End Sub

	Public Shared Function BatchIncomeSummary(ByVal startDate As Date, ByVal endDate As Date) As Entity.ResultProcedureItem
		Dim conn As New OracleConnection(Common.Utility.ConnectString)
		conn.Open()
		Dim cmd As OracleCommand = conn.CreateCommand
		cmd.CommandType = System.Data.CommandType.StoredProcedure
		cmd.CommandText = Common.Utility.Schema & ".RNWP_BATCH_INCOME_SUMMARY"

		Dim param1 As New OracleParameter
		param1.ParameterName = "P_START_DATE"
		param1.OracleType = OracleType.VarChar
		param1.Direction = ParameterDirection.Input
		param1.Value = startDate.ToString("yyyyMMdd")

		Dim param2 As New OracleParameter
		param2.ParameterName = "P_END_DATE"
		param2.OracleType = OracleType.VarChar
		param2.Direction = ParameterDirection.Input
		param2.Value = endDate.ToString("yyyyMMdd")

		Dim param3 As New OracleParameter
		param3.ParameterName = "P_STR_ERROR_CODE"
		param3.OracleType = OracleType.VarChar
		param3.Direction = ParameterDirection.Output
		param3.Size = 1000

		Dim param4 As New OracleParameter
		param4.ParameterName = "P_STR_ERROR_MSG"
		param4.OracleType = OracleType.VarChar
		param4.Direction = ParameterDirection.Output
		param4.Size = 1000

		cmd.Parameters.Add(param1)
		cmd.Parameters.Add(param2)
		cmd.Parameters.Add(param3)
		cmd.Parameters.Add(param4)

		cmd.ExecuteNonQuery()

		Dim rslt As New Entity.ResultProcedureItem
		rslt.ErrorCode = param3.Value.ToString.Trim
		rslt.ErrorMessage = param4.Value.ToString.Trim

		Return rslt
	End Function

	Public Shared Function BatchOfficerExpird(ByVal startDate As Date, ByVal endDate As Date) As Entity.ResultProcedureItem
		Dim conn As New OracleConnection(Common.Utility.ConnectString)
		conn.Open()
		Dim cmd As OracleCommand = conn.CreateCommand
		cmd.CommandType = System.Data.CommandType.StoredProcedure
		cmd.CommandText = Common.Utility.Schema & ".RNWP_BATCH_OFFICER_EXPIRD"

		Dim param1 As New OracleParameter
		param1.ParameterName = "P_START_DATE"
		param1.OracleType = OracleType.VarChar
		param1.Direction = ParameterDirection.Input
		param1.Value = startDate.ToString("yyyyMMdd")

		Dim param2 As New OracleParameter
		param2.ParameterName = "P_END_DATE"
		param2.OracleType = OracleType.VarChar
		param2.Direction = ParameterDirection.Input
		param2.Value = endDate.ToString("yyyyMMdd")

		Dim param3 As New OracleParameter
		param3.ParameterName = "P_STR_ERROR_CODE"
		param3.OracleType = OracleType.VarChar
		param3.Direction = ParameterDirection.Output
		param3.Size = 1000

		Dim param4 As New OracleParameter
		param4.ParameterName = "P_STR_ERROR_MSG"
		param4.OracleType = OracleType.VarChar
		param4.Direction = ParameterDirection.Output
		param4.Size = 1000

		cmd.Parameters.Add(param1)
		cmd.Parameters.Add(param2)
		cmd.Parameters.Add(param3)
		cmd.Parameters.Add(param4)

		cmd.ExecuteNonQuery()

		Dim rslt As New Entity.ResultProcedureItem
		rslt.ErrorCode = param3.Value.ToString.Trim
		rslt.ErrorMessage = param4.Value.ToString.Trim

		Return rslt
	End Function

	Public Shared Function BatchReconcileFile(ByVal startDate As Date, ByVal endDate As Date) As Entity.ResultProcedureItem
		Dim conn As New OracleConnection(Common.Utility.ConnectString)
		conn.Open()
		Dim cmd As OracleCommand = conn.CreateCommand
		cmd.CommandType = System.Data.CommandType.StoredProcedure
		cmd.CommandText = Common.Utility.Schema & ".RNWP_BATCH_RECONCILE_FILE"

		Dim param1 As New OracleParameter
		param1.ParameterName = "P_START_DATE"
		param1.OracleType = OracleType.VarChar
		param1.Direction = ParameterDirection.Input
		param1.Value = startDate.ToString("yyyyMMdd")

		Dim param2 As New OracleParameter
		param2.ParameterName = "P_END_DATE"
		param2.OracleType = OracleType.VarChar
		param2.Direction = ParameterDirection.Input
		param2.Value = endDate.ToString("yyyyMMdd")

		Dim param3 As New OracleParameter
		param3.ParameterName = "P_STR_ERROR_CODE"
		param3.OracleType = OracleType.VarChar
		param3.Direction = ParameterDirection.Output
		param3.Size = 1000

		Dim param4 As New OracleParameter
		param4.ParameterName = "P_STR_ERROR_MSG"
		param4.OracleType = OracleType.VarChar
		param4.Direction = ParameterDirection.Output
		param4.Size = 1000

		cmd.Parameters.Add(param1)
		cmd.Parameters.Add(param2)
		cmd.Parameters.Add(param3)
		cmd.Parameters.Add(param4)

		cmd.ExecuteNonQuery()

		Dim rslt As New Entity.ResultProcedureItem
		rslt.ErrorCode = param3.Value.ToString.Trim
		rslt.ErrorMessage = param4.Value.ToString.Trim

		Return rslt
	End Function

	Public Shared Function Online_IVR_XML(ByVal P_TRAN_ID As String, ByVal P_MOBILE_NO As String,
	  ByVal P_PP_CODE As String, ByVal P_TOPUP_VALUE As String,
	  ByVal P_TOPUP_DATE As String, ByVal P_REF1_IVR As String, ByVal P_REQ_TRAN_ID As String) As Entity.ResultProcedureItem
		Dim conn As New OracleConnection(Common.Utility.ConnectString)
		conn.Open()
		Dim cmd As OracleCommand = conn.CreateCommand
		cmd.CommandType = System.Data.CommandType.StoredProcedure
		cmd.CommandText = Common.Utility.Schema & ".RNWP_ONLINE_IVR_XML"

		Dim param1 As New OracleParameter
		param1.ParameterName = "P_TRAN_ID"
		param1.OracleType = OracleType.VarChar
		param1.Direction = ParameterDirection.Input
		param1.Value = P_TRAN_ID

		Dim param2 As New OracleParameter
		param2.ParameterName = "P_MOBILE_NO"
		param2.OracleType = OracleType.VarChar
		param2.Direction = ParameterDirection.Input
		param2.Value = P_MOBILE_NO

		Dim param3 As New OracleParameter
		param3.ParameterName = "P_PP_CODE"
		param3.OracleType = OracleType.VarChar
		param3.Direction = ParameterDirection.Input
		param3.Value = P_PP_CODE

		Dim param4 As New OracleParameter
		param4.ParameterName = "P_TOPUP_VALUE"
		param4.OracleType = OracleType.VarChar
		param4.Direction = ParameterDirection.Input
		param4.Value = P_TOPUP_VALUE

		Dim param5 As New OracleParameter
		param5.ParameterName = "P_TOPUP_DATE"
		param5.OracleType = OracleType.VarChar
		param5.Direction = ParameterDirection.Input
		param5.Value = P_TOPUP_DATE

		Dim param6 As New OracleParameter
		param6.ParameterName = "P_REF1_IVR"
		param6.OracleType = OracleType.VarChar
		param6.Direction = ParameterDirection.Input
		param6.Value = P_REF1_IVR

		Dim param7 As New OracleParameter
		param7.ParameterName = "P_REQ_TRAN_ID"
		param7.OracleType = OracleType.VarChar
		param7.Direction = ParameterDirection.Input
		param7.Value = P_REQ_TRAN_ID

		Dim param8 As New OracleParameter
		param8.ParameterName = "P_STR_ERROR_CODE"
		param8.OracleType = OracleType.VarChar
		param8.Direction = ParameterDirection.Output
		param8.Size = 1000

		Dim param9 As New OracleParameter
		param9.ParameterName = "P_STR_ERROR_MSG"
		param9.OracleType = OracleType.VarChar
		param9.Direction = ParameterDirection.Output
		param9.Size = 1000

		cmd.Parameters.Add(param1)
		cmd.Parameters.Add(param2)
		cmd.Parameters.Add(param3)
		cmd.Parameters.Add(param4)
		cmd.Parameters.Add(param5)
		cmd.Parameters.Add(param6)
		cmd.Parameters.Add(param7)
		cmd.Parameters.Add(param8)
		cmd.Parameters.Add(param9)

		cmd.ExecuteNonQuery()

		Dim rslt As New Entity.ResultProcedureItem
		rslt.ErrorCode = param8.Value.ToString.Trim
		rslt.ErrorMessage = param9.Value.ToString.Trim

		Return rslt
	End Function

End Class
